package com.food.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.MessageDAO;
import com.food.model.Message;

public class MessageDAOJdbc implements MessageDAO {

//	private static final String URL = "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
//	private static final String USER = "sa";
//	private static final String PASSWORD = "sa123456";
	
	private DataSource ds = null;
	public MessageDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private static final String INSERT = "insert into message (rid, mbid, messageword) values(?,?,?)";

	@Override
	public void insertMessage(Message bean) {

		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(INSERT);

			stmt.setInt(1, bean.getRid());
			stmt.setInt(2, bean.getMbid());
			stmt.setString(3, bean.getMessageword());

			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String UPDATE = "update message set rid =?, mbid =?, messageword =? where messageid =?";

	@Override
	public void updateMessage(int rid, int mbid, String messageword,
			int messageid) {
		Message result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(UPDATE);

			stmt.setInt(1, rid);
			stmt.setInt(2, mbid);
			stmt.setString(3, messageword);
			stmt.setInt(4, messageid);

			int i = stmt.executeUpdate();
			if (i == 1) {
				result = new Message();
				result.setRid(rid);
				result.setMbid(mbid);
				result.setMessageword(messageword);
				result.setMessageid(messageid);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String DELETE = "delete from message where messageid =?";

	@Override
	public void deleteMessage(int messageid) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(DELETE);

			stmt.setInt(1, messageid);

			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String SELECT_BY_RID = "select messageid ,rid, mbid, messageword from message where rid=?";

	@Override
	public List<Message> findMessageByRecipe(int rid) {
		List<Message> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_RID);
			stmt.setInt(1, rid);
			rset = stmt.executeQuery();

			result = new ArrayList<Message>();
			while (rset.next()) {
				Message temp = new Message();
				temp.setMessageid(rset.getInt("messageid"));
				temp.setRid(rset.getInt("rid"));
				temp.setMbid(rset.getInt("mbid"));
				temp.setMessageword(rset.getString("messageword"));

				result.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String SELECT_BY_MB = "select messageid ,rid, mbid, messageword from message where mbid=?";

	public List<Message> findMessageByMb(int mbid){
		List<Message> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_MB);
			stmt.setInt(1, mbid);
			rset = stmt.executeQuery();

			result = new ArrayList<Message>();
			while (rset.next()) {
				Message temp = new Message();
				temp.setMessageid(rset.getInt("messageid"));
				temp.setRid(rset.getInt("rid"));
				temp.setMbid(rset.getInt("mbid"));
				temp.setMessageword(rset.getString("messageword"));

				result.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
		
	}
	
	public static void main(String[] args) {
		// select cid
		MessageDAO dao = new MessageDAOJdbc();
		List<Message> bean = dao.findMessageByRecipe(1);
		System.out.println(bean);

		// // select mbid
		// List<Application> bean1 = dao.findApplicationByMb(1);
		// System.out.println(bean1);
		//
		// // SELECT ALL
		// List<Application> bean2 = dao.findAllApplication();
		// System.out.println(bean2);
		//
		// delete
		// dao.deleteMessage(6);

		// insert
		// Message bean3 = new Message();
		// bean3.setMbid(4);
		// bean3.setRid(9);
		// bean3.setMessageword("one two three four five");
		// dao.insertMessage(bean3);
		// System.out.println(bean3);
		//
		// update
		dao.updateMessage(7, 2, "1", 3);

	}

}
